﻿using System;
using System.Collections.Generic;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Text;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Utils;
using System.Data.SqlClient;
using Vote.Models;
using System.Data.Entity;


namespace Dal
{
    public class DalERPCommon:IDalCommon
    {
        /// <summary>
        /// 新增实体表
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int AddNewEntity<T>(string tableName,T obj) where T : class
        {
            using (var db=new ERPDbContext())
            {
                try
                {
                    DbEntityEntry<T> entry = db.Entry<T>(obj);
                    entry.State = System.Data.Entity.EntityState.Added;
                    return db.SaveChanges();

                }
                catch (Exception ex)
                {
                    LogHelper.WriteErrorLog("添加实体类" + tableName + "失败:" + ex.Message);
                    return -1;
                }
            }
        }

        /// <summary>
        /// 通过id删除某一条信息
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteEntity<T>(string tableName, long id) where T : class
        {
            using (var db=new ERPDbContext())
            {
                try
                {

                    T obj= db.Set<T>().Find(id);
                    DbEntityEntry<T> entry = db.Entry<T>(obj);
                    entry.State = System.Data.Entity.EntityState.Deleted;

                    return db.SaveChanges();
                }
                catch (Exception ex)
                {
                    LogHelper.WriteErrorLog("删除实体类" + tableName + "失败:" + ex.Message);
                    return -1;
                }
            }
        }

        /// <summary>
        /// 删除实体类
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <param name="sql"></param>
        public int DeleteEntityBySql(string tableName,string sql)
        {
            using (var db=new ERPDbContext())
            {
                try
                {
                    db.Database.ExecuteSqlCommand(sql);

                    return db.SaveChanges();
                }
                catch (Exception ex)
                {
                    LogHelper.WriteErrorLog("删除实体类" + tableName + "失败:" + ex.Message);
                    return -1;
                }
            }
        }
       
       
        /// <summary>
        /// 修改实体类
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <param name="tableName"></param>
        public int UpdateEntity<T>(string tableName,T obj)where T:class
        {

            using (var db=new ERPDbContext())
            {
                try
                {
                    DbEntityEntry<T> entry = db.Entry<T>(obj);
                    entry.State = System.Data.Entity.EntityState.Modified;

                    return db.SaveChanges();

                }
                catch (Exception ex)
                {
                    LogHelper.WriteErrorLog("修改实体类" + tableName + "失败:" + ex.Message);
                    return -1;
                }
            }
        }

        /// <summary>
        /// 更新实体类
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="sql"></param>
        public int UpdateEntityBySql(string tableName, string sql)
        {
            using (var db=new ERPDbContext())
            {
                try
                {
                    db.Database.ExecuteSqlCommand(sql);

                    return db.SaveChanges();
                }
                catch (Exception ex)
                {
                    LogHelper.WriteErrorLog("更新实体类" + tableName + "失败:" + ex.Message);
                    return -1;
                }
            }
        }

        /// <summary>
        /// 查询实体类
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public T SelectEntity<T>(string tableName,long id) where T : class
        {
            using (var db=new ERPDbContext())
            {
                try
                {
                    T obj = db.Set<T>().Find(id);

                    return obj;
                }
                catch (Exception ex)
                {
                    LogHelper.WriteErrorLog("查询实体类" + tableName + "失败:" + ex.Message);
                    return null;
                }
            }
        }

        public int GetCountBySQL(string tableName, string sql)
        {
            IList<int> comList = new List<int>();
            using (var db=new ERPDbContext())
            {
                try
                {
                    var result = db.Database.SqlQuery<int>(sql);
                    comList = result.ToList();

                }
                catch (Exception ex)
                {
                    LogHelper.WriteErrorLog("查询实体类数量" + tableName + "失败:" + ex.Message);
                }
                if (comList.Count > 0)
                {
                   return comList[0];
                }
                else
                {
                    return 0;
                }
            }      
        }
  
        /// <summary>
        /// 查询实体类
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<T> SelectEntityBySql<T>(string tableName, string sql) where T : class
        {
            using (var db=new ERPDbContext())
            {
                try
                {
                    var result = db.Database.SqlQuery<T>(sql);

                    return result.ToList();
                }
                catch (Exception ex)
                {
                    LogHelper.WriteErrorLog("查询实体类" + tableName + "失败:" + ex.Message);
                    return null;
                }
            }
        }

        /// <summary>
        /// 获取用户自定义字段
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="sql">只能获取一个字段,这个字段可以返回单个值也可以返回一列值</param>
        /// <param name="type">基本类型 如 string int long bool decimal</param>
        /// <param name="isList">是否返回列表 true:返回一列  false:单个值</param>
        /// <returns></returns>
        public Object SelectSingleValueBySql(string tableName, string sql, string type = "string", bool isList = false)
        {
            using (var db = new ERPDbContext())
            {
                try
                {
                    if (type == "string")
                    {
                        var result = db.Database.SqlQuery<string>(sql);
                        if (isList)
                        {
                            return result.ToList();
                        }
                        else
                        {
                            return result.Single();
                        }
                    }
                    else if (type == "int")
                    {
                        var result = db.Database.SqlQuery<int>(sql);
                        if (isList)
                        {
                            return result.ToList();
                        }
                        else
                        {
                            return result.Single();
                        }
                    }
                    else if (type == "long")
                    {
                        var result = db.Database.SqlQuery<long>(sql);
                        if (isList)
                        {
                            return result.ToList();
                        }
                        else
                        {
                            return result.Single();
                        }
                    }
                    else if (type == "decimal")
                    {
                        var result = db.Database.SqlQuery<decimal>(sql);
                        if (isList)
                        {
                            return result.ToList();
                        }
                        else
                        {
                            return result.Single();
                        }
                    }
                    else if (type == "bool")
                    {
                        var result = db.Database.SqlQuery<bool>(sql);
                        if (isList)
                        {
                            return result.ToList();
                        }
                        else
                        {
                            return result.Single();
                        }
                    }
                    else
                    {
                        var result = db.Database.SqlQuery<object>(sql);
                        if (isList)
                        {
                            return result.ToList();
                        }
                        else
                        {
                            return result.Single();
                        }
                    }

                }
                catch (Exception ex)
                {
                    LogHelper.WriteErrorLog("查询实体类" + tableName + "失败:" + ex.Message);
                    return null;
                }
            }
        }
        public List<T> SelectEntityInfoByQueryWithPager<T>(string tableName, Dictionary<string, string> conditionFiledValue, int currentPage, int pageSize, string orderByFiled, bool desc, out int totalPage) where T : new()
        {
            totalPage=GetPageCount(tableName,conditionFiledValue,pageSize);
            List<T> comList = new List<T>();
            try
            {
                using (var db=new ERPDbContext())
                {
                    int startNum = (currentPage - 1) * pageSize + 1;
                    int endNum = (startNum + pageSize) - 1;
                    string orderStr = " ORDER BY [" + orderByFiled + "] ";
                    if (desc == true)
                    {
                        orderStr = orderStr + " DESC ";
                    }
                    string strSetConditions = this.GetSetConditionsStr(conditionFiledValue);

                    string sql = @"WITH t AS (SELECT ROW_NUMBER() OVER(" + orderStr + ") as [row_number],* FROM [" + tableName + "] ";

                    if (!string.IsNullOrEmpty(strSetConditions))
                    {
                        sql += " Where " + strSetConditions+")";
                    }
                    else
                    {
                        sql += ")";
                    }

                    sql = sql + " Select * from t where [row_number] BETWEEN " + startNum.ToString() + " and " + endNum.ToString();

                 
                    List<SqlParameter> ilistStr = new List<SqlParameter>(); 

                    foreach (KeyValuePair<string, string> item in conditionFiledValue)
                    {
                         ilistStr.Add(new SqlParameter("@"+item.Key,item.Value));
                    }

                    SqlParameter[] param = ilistStr.ToArray();

                    var result=db.Database.SqlQuery<T>(sql,param);

                    comList = result.ToList();
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrorLog("查询实体类" + tableName + "失败:" + ex.Message);
                return null;
            }
            return comList;
        }

       private int  GetPageCount(string tableName, Dictionary<string, string> conditionFiledValue,int pageSize)
       {
           int totalPage = 0;
           try
           {
               using (var db=new ERPDbContext())
               {
                   string strSetConditions = this.GetSetConditionsStr(conditionFiledValue);
                   string sqlCount = "select count(*)  FROM [" + tableName + "]";
                   if (!string.IsNullOrEmpty(strSetConditions))
                   {
                       sqlCount+=" Where " + strSetConditions;
                   }
                  
                   List<SqlParameter> ilistStr = new List<SqlParameter>();

                   foreach (KeyValuePair<string, string> item in conditionFiledValue)
                   {
                       ilistStr.Add(new SqlParameter("@" + item.Key, item.Value));
                   }

                   SqlParameter[] param = ilistStr.ToArray();

                   var resultCount_ = db.Database.SqlQuery<int>(sqlCount, param);

                   int resultCount = resultCount_.ToList().First();

                   totalPage = (resultCount % pageSize) == 0 ? (resultCount / pageSize) : (resultCount / pageSize) + 1;
               }
           }
           catch (Exception ex)
           {
               LogHelper.WriteErrorLog("查询实体类" + tableName + "失败:" + ex.Message);
           }

           return totalPage;
       }


        /// <summary>
        /// 设置条件值Str
        /// </summary>
        /// <param name="conditionFiledValue"></param>
        /// <returns></returns>
        private string GetSetConditionsStr(Dictionary<string, string> conditionFiledValue)
        {
            string retStr = string.Empty;
            foreach (var item in conditionFiledValue)
            {
                retStr = retStr + " [" + item.Key + "] =@" + item.Key + " and ";
            }

            if (!String.IsNullOrEmpty(retStr))
            {
                retStr = retStr.Substring(0, retStr.LastIndexOf(" and "));
            }
            return retStr;
        }

    }
}
